IF OBJECT_ID('dbo.RptPCROIDEPMANumber') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptPCROIDEPMANumber
    IF OBJECT_ID('dbo.RptPCROIDEPMANumber') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptPCROIDEPMANumber >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptPCROIDEPMANumber >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptPCROIDEPMANumber
--
-- Author:      Arthur Bianchini
--
-- Create date: 10/9/2009
--
-- Description: 
--                  
--
-- Parameters:  
--     @Institution             
--     @Department    
--	   @Unit          
--     @PrincipalInvestigator        
--     @domainUserId            
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- 
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/9/09     aab        Created
-- =======================================================================
CREATE PROCEDURE dbo.RptPCROIDEPMANumber 
     (
     @Institution									VARCHAR(50) = NULL, 
	 @Department								VARCHAR(250) = NULL,    
	 @Unit											VARCHAR(250)  = NULL,
     @PrincipalInvestigator				VARCHAR(2000) = NULL,    
     @Sponsor									VARCHAR(2000) = NULL,      
	 @OriginatingSponsor                 VARCHAR(2000) = NULL,  
	 @FundNumber							 VARCHAR(1000) = NULL, 
     @DomainUserId							INT
     )                        
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error INT,
               @RowCount INT
               
     -- ALL checking

     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
	 IF @Unit IN ('ALL','','0') SET @Unit = NULL
     IF @PrincipalInvestigator IN ('ALL','','0') SET @PrincipalInvestigator = NULL
     IF @Sponsor IN ('ALL','','0') SET @Sponsor = NULL
	 IF @OriginatingSponsor IN ('ALL','','0') SET @OriginatingSponsor = NULL
	 IF @FundNumber IN ('ALL','','0') SET @FundNumber = NULL 

CREATE TABLE   #Proposal 
								(PropNo varchar(10))

CREATE CLUSTERED INDEX IX_#Proposal_PropNo
    ON #Proposal (PropNo); 

     INSERT INTO #Proposal
     
     SELECT sec.InfoEdPropNumber
     FROM INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements(@DomainUserId) sec 
	 
SELECT DISTINCT
p.ProposalNumber,
u.p_chr_32 as 'IDE/PMANumber',
case
when u2.p_chr_33 in ('',null) THEN null
else convert(datetime,u2.p_chr_33)  
end as IDEPMANumberExpirationDate,
pma.instaccountno as PSFundNumber,
p.Institution, 
p.DepartmentChiefCode,
p.Department,
p.UnitChiefCode,
p.Unit,
p.PrincipalInvestigator,  
p.SponsorName, 
p.OriginatingSponsor,
p.ProposalStatus,
p.ProposalTitle
FROM vwMasterProposalsData p
INNER JOIN #Proposal pf 
	ON p.PropNo = pf.PropNo
LEFT OUTER JOIN pt_acct a 
	ON	a.prop_no = p.PropNo
	AND	a.inst_code = p.instcode
	AND	len(a.hacct_no) = 6
LEFT OUTER JOIN pmaccount pma 
	ON a.hacct_no = pma.instaccountno  
INNER JOIN	prop_u u--'IDE/PMA Number'
	ON	p.instcode = u.inst_code
	AND	p.propno = u.prop_no
LEFT OUTER JOIN		prop_u u2--'IDE/PMANumber-ExpirationDate'
	ON	p.instcode = u2.inst_code
	AND	p.propno = u2.prop_no
 INNER JOIN INSIGHT_RPT_DB.dbo.AgrAgreement_Info ai
			ON p.ProposalNumber = ai.FolderNumber
WHERE	((u.p_chr_32 is not null and u.p_chr_32 <> '')
			OR	(u2.p_chr_33 is null and u2.p_chr_33 <> ''))
AND
	   (ai.institutionid= @Institution  OR @Institution IS NULL OR @Institution = 0) AND 
	   (ai.DepartmentId = @Department OR @Department IS NULL OR @Department=0 ) AND 
	   (ai.UnitId in (SELECT * FROM dbo.fnSplitFundNumbers(@Unit)) OR @Unit IS NULL OR @Unit='0' OR @Unit='') AND
       (ai.PrincipalInvestigatorId = @PrincipalInvestigator OR @PrincipalInvestigator IS NULL OR @PrincipalInvestigator=''OR @PrincipalInvestigator='0') AND   
	   (@Sponsor IS NULL OR p.SponCode IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @Sponsor ))) AND
       (@OriginatingSponsor IS NULL OR p.OriginatingSponCode IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @OriginatingSponsor ))) AND
	   (@FundNumber IS NULL OR pma.instaccountno IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@FundNumber)))          
ORDER BY IDEPMANumberExpirationDate desc,p.ProposalStatus,p.Institution,u.p_chr_32 
    
SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
IF @Error!=0
     RETURN -1
      
  -- Return Success
RETURN 0

     
END
GO

IF OBJECT_ID('dbo.RptPCROIDEPMANumber') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptPCROIDEPMANumber >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptPCROIDEPMANumber>>>'
GO

IF  EXISTS
    (SELECT name
     FROM sys.database_principals
     WHERE name = 'InsightReport')
BEGIN
	GRANT EXECUTE ON dbo.RptPCROIDEPMANumber TO InsightReport
END


GO


